package com.zbl.jdbc;

import java.sql.*;

public class ConManager2 {
	public static String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
	public static String URL = "jdbc:sqlserver://139.159.163.121:2018;DatabaseName=tigerzz;";
	public static String USER = "sa";
	public static String PASSWORD = "Hxj198677";

	// 查询积分
	public static String selectByte(String te, String bh) {
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		String i = "-1";
		try {
			Class.forName(DRIVER);
			con = DriverManager.getConnection(URL, USER, PASSWORD);
			if (!"".equals(te) && null != te) {
				pst = con.prepareStatement("select * from dbo.pos_vip_info where mobile_no=?");
				pst.setString(1, te);
				rs = pst.executeQuery();
				while (rs.next()) {
					String mobile = rs.getString("mobile_no");
					String vip_total_integral = rs.getString("vip_total_integral");
					System.out.println("mobile:" + mobile);
					i = vip_total_integral;
					System.out.println("vip_total_integral:" + vip_total_integral);
					return i;
				}
			}


			if (!"".equals(bh) && null != bh) {
				pst = con.prepareStatement("select * from dbo.pos_vip_info where card_id=?");
				pst.setString(1, bh);
				rs = pst.executeQuery();
				while (rs.next()) {
					String mobile = rs.getString("mobile_no");
					String vip_total_integral = rs.getString("vip_total_integral");
					System.out.println("mobile:" + mobile);
					i = vip_total_integral;
					System.out.println("vip_total_integral:" + vip_total_integral);
					return i;
				}

			}

			if (rs != null) {
				rs.close();
				rs = null;
			}
			if (pst != null) {
				pst.close();
				pst = null;
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} // sql server数据库
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}


	// 查询原来的积分
	public static String selectByteYLJF(String te) {
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			Class.forName(DRIVER);
			con = DriverManager.getConnection(URL, USER, PASSWORD);
			pst = con.prepareStatement("select * from dbo.pos_vip_info where mobile_no=?");
			pst.setString(1, te);
			// String st="15888341126";
			// pst.setString(1,st);
			// pst.setString(1,"");// 设置第1个问号对应的参数
			rs = pst.executeQuery();
			while (rs.next()) {
				String mobile = rs.getString("mobile_no");
				String vip_total_integral = rs.getString("vip_total_integral");
				System.out.println("mobile:" + mobile);
				System.out.println("原来积分vip_total_integral:" + vip_total_integral);
				return vip_total_integral;
			}

			pst = con.prepareStatement("select * from dbo.pos_vip_info where card_id=?");
			pst.setString(1, te);
			rs = pst.executeQuery();
			while (rs.next()) {
				String mobile = rs.getString("mobile_no");
				String vip_total_integral = rs.getString("vip_total_integral");
				System.out.println("mobile:" + mobile);
				System.out.println("原来积分vip_total_integral:" + vip_total_integral);
				return vip_total_integral;
			}


			if (rs != null) {
				rs.close();
				rs = null;
			}
			if (pst != null) {
				pst.close();
				pst = null;
			}

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} // sql server数据库
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return "0";
	}


	// 查询消费金额
	public static String selectByteXFJE(String te) {
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			Class.forName(DRIVER);
			con = DriverManager.getConnection(URL, USER, PASSWORD);

			pst = con.prepareStatement("select * from dbo.pos_vip_info where mobile_no=?");
			pst.setString(1, te);
			// String st="15888341126";
			// pst.setString(1,st);
			// pst.setString(1,"");// 设置第1个问号对应的参数
			rs = pst.executeQuery();
			while (rs.next()) {
				String mobile = rs.getString("mobile_no");
				String vip_cons_amt = rs.getString("vip_cons_amt");
				System.out.println("mobile:" + mobile);
				System.out.println("vip_cons_amt:" + vip_cons_amt);
				return vip_cons_amt;
			}


			pst = con.prepareStatement("select * from dbo.pos_vip_info where card_id=?");
			pst.setString(1, te);
			// String st="15888341126";
			// pst.setString(1,st);
			// pst.setString(1,"");// 设置第1个问号对应的参数
			rs = pst.executeQuery();
			while (rs.next()) {
				String mobile = rs.getString("mobile_no");
				String vip_cons_amt = rs.getString("vip_cons_amt");
				System.out.println("mobile:" + mobile);
				System.out.println("vip_cons_amt:" + vip_cons_amt);
				return vip_cons_amt;
			}




			if (rs != null) {
				rs.close();
				rs = null;
			}
			if (pst != null) {
				pst.close();
				pst = null;
			}

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} // sql server数据库
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return "0";
	}


	//更新数据

	// 更新 2  积分
	public static void update2(int integral, String telephone) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			Class.forName(DRIVER);
			con = DriverManager.getConnection(URL, USER, PASSWORD);
			String sql = "update dbo.pos_vip_info set vip_total_integral='" + integral + "' where card_id='" + telephone + "'";
			int i = 0;
			try {
				ps = (PreparedStatement) con.prepareStatement(sql);
				i = ps.executeUpdate();
				System.out.println("resutl: " + i);
				ps.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}


		} catch (SQLException e) {
			System.out.println("操作失败o(╥﹏╥");
			e.printStackTrace();
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} finally {

		}

	}


	// 更新 1  积分
	public static void update1(int integral, String telephone) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			Class.forName(DRIVER);
			con = DriverManager.getConnection(URL, USER, PASSWORD);
			String sql = "update dbo.pos_vip_info set vip_total_integral='" + integral + "' where mobile_no='" + telephone + "'";
			int i = 0;
			try {
				ps = (PreparedStatement) con.prepareStatement(sql);
				i = ps.executeUpdate();
				System.out.println("resutl: " + i);
				ps.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}


		} catch (SQLException e) {
			System.out.println("操作失败o(╥﹏╥");
			e.printStackTrace();
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} finally {

		}

	}




	// 更新  金额3
	public static void update3(int integral, String telephone) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			Class.forName(DRIVER);
			con = DriverManager.getConnection(URL, USER, PASSWORD);
			String sql = "update dbo.pos_vip_info set vip_cons_amt='" + integral + "' where mobile_no='" + telephone + "'";
			int i = 0;
			try {
				ps = (PreparedStatement) con.prepareStatement(sql);
				i = ps.executeUpdate();
				System.out.println("resutl: " + i);
				ps.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (SQLException e) {
			System.out.println("操作失败o(╥﹏╥");
			e.printStackTrace();
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} finally {

		}

	}


	// 更新  金额4
	public static void update4(int integral, String telephone) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			Class.forName(DRIVER);
			con = DriverManager.getConnection(URL, USER, PASSWORD);
			String sql = "update dbo.pos_vip_info set vip_cons_amt='" + integral + "' where card_id='" + telephone + "'";
			int i = 0;
			try {
				ps = (PreparedStatement) con.prepareStatement(sql);
				i = ps.executeUpdate();
				System.out.println("resutl: " + i);
				ps.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (SQLException e) {
			System.out.println("操作失败o(╥﹏╥");
			e.printStackTrace();
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} finally {

		}

	}



	public static void main(String[] args) throws SQLException {
		
		//查询
		String jf=ConManager2.selectByte("","15888341126");
		System.out.println("jf:" + jf);
	
//		//加积分
//		Float f=Float.valueOf(jf);
//		int f2=f.intValue()+200;
//		ConManager2.update(f2,"15888341126");
		
		//加消费金额
		 //获取消费金额
        String s=ConManager2.selectByteXFJE("15888341126");
        //累加
        Float f=Float.valueOf(s);
		int f2=f.intValue()+1000;
		ConManager2.update2(f2,"15888341126");
      

		

	}
}
